package com.ts.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.*;
import java.sql.Date;
import java.util.*;

public class NamedParameterStatement {
	
	public static void main(String[] args) {
		Connection con=null;
		NamedParameterStatement pst=null;
		ResultSet rs=null;
        try {
        	String sDriverName = "oracle.jdbc.driver.OracleDriver";
			Class.forName(sDriverName);
			con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/orcl", "gmp", "gmp");
			
			String sql="select fName from s_Department where fCode=:deptcode order by 1";
			pst=new NamedParameterStatement(con, sql);
			pst.setString("deptcode", "261.10");
			rs=pst.executeQuery();
			if(rs.next()){
				System.out.println(rs.getString(1));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pst.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private final PreparedStatement statement;

	@SuppressWarnings("rawtypes")
	private final Map indexMap;

	@SuppressWarnings("rawtypes")
	public NamedParameterStatement(Connection connection, String query)
			throws SQLException {
		indexMap = new HashMap();
		String parsedQuery = parse(query, indexMap);
		statement = connection.prepareStatement(parsedQuery);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	static final String parse(String query, Map paramMap) {

		int length = query.length();
		StringBuffer parsedQuery = new StringBuffer(length);
		boolean inSingleQuote = false;
		boolean inDoubleQuote = false;
		int index = 1;

		for (int i = 0; i < length; i++) {
			char c = query.charAt(i);
			if (inSingleQuote) {
				if (c == '\'') {
					inSingleQuote = false;
				}
			} else if (inDoubleQuote) {
				if (c == '"') {
					inDoubleQuote = false;
				}
			} else {
				if (c == '\'') {
					inSingleQuote = true;
				} else if (c == '"') {
					inDoubleQuote = true;
				} else if (c == ':' && i + 1 < length
						&& Character.isJavaIdentifierStart(query.charAt(i + 1))) {
					int j = i + 2;
					while (j < length
							&& Character.isJavaIdentifierPart(query.charAt(j))) {
						j++;
					}
					String name = query.substring(i + 1, j);
					c = '?';
					i += name.length();

					List indexList = (List) paramMap.get(name);
					if (indexList == null) {
						indexList = new LinkedList();
						paramMap.put(name, indexList);
					}
					indexList.add(new Integer(index));

					index++;
				}
			}
			parsedQuery.append(c);
		}

		for (Iterator itr = paramMap.entrySet().iterator(); itr.hasNext();) {
			Map.Entry entry = (Map.Entry) itr.next();
			List list = (List) entry.getValue();
			int[] indexes = new int[list.size()];
			int i = 0;
			for (Iterator itr2 = list.iterator(); itr2.hasNext();) {
				Integer x = (Integer) itr2.next();
				indexes[i++] = x.intValue();
			}
			entry.setValue(indexes);
		}

		return parsedQuery.toString();
	}

	private int[] getIndexes(String name) {
		int[] indexes = (int[]) indexMap.get(name);
		if (indexes == null) {
			throw new IllegalArgumentException("Parameter not found: " + name);
		}
		return indexes;
	}

	public void setArray(String name, Array value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setArray(index, value);
		}
	}

	public void setAsciiStream(String name, InputStream value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setAsciiStream(index, value);
		}
	}

	public void setAsciiStream(String name, InputStream value, int length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setAsciiStream(index, value, length);
		}
	}

	public void setBigDecimal(String name, BigDecimal value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBigDecimal(index, value);
		}
	}

	public void setBinaryStream(String name, InputStream value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBinaryStream(index, value);
		}
	}

	public void setBinaryStream(String name, InputStream value, int length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBinaryStream(index, value, length);
		}
	}

	public void setBinaryStream(String name, InputStream value, long length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBinaryStream(index, value, length);
		}
	}

	public void setBlob(String name, Blob value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBlob(index, value);
		}
	}

	public void setBlob(String name, InputStream value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBlob(index, value);
		}
	}

	public void setBlob(String name, InputStream value, long length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBlob(index, value, length);
		}
	}

	public void setBoolean(String name, boolean value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBoolean(index, value);
		}
	}

	public void setByte(String name, byte value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setByte(index, value);
		}
	}

	public void setBytes(String name, byte[] value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setBytes(index, value);
		}
	}

	public void setCharacterStream(String name, Reader value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setCharacterStream(index, value);
		}
	}

	public void setCharacterStream(String name, Reader value, int length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setCharacterStream(index, value, length);
		}
	}

	public void setCharacterStream(String name, Reader value, long length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setCharacterStream(index, value, length);
		}
	}

	public void setClob(String name, Clob value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setClob(index, value);
		}
	}

	public void setClob(String name, Reader value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setClob(index, value);
		}
	}

	public void setClob(String name, Reader value, long length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setClob(index, value, length);
		}
	}

	public void setDate(String name, Date value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setDate(index, value);
		}
	}

	public void setDate(String name, Date value, Calendar cal) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setDate(index, value, cal);
		}
	}

	public void setDouble(String name, double value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setDouble(index, value);
		}
	}

	public void setFloat(String name, float value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setFloat(index, value);
		}
	}

	public void setInt(String name, int value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setInt(index, value);
		}
	}

	public void setLong(String name, long value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setLong(index, value);
		}
	}

	public void setNCharacterStream(String name, Reader value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setNCharacterStream(index, value);
		}
	}

	public void setNCharacterStream(String name, Reader value, long length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setNCharacterStream(index, value, length);
		}
	}

	public void setNClob(String name, NClob value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setNClob(index, value);
		}
	}

	public void setNClob(String name, Reader value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setNClob(index, value);
		}
	}

	public void setNClob(String name, Reader value, long length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setNClob(index, value, length);
		}
	}

	public void setNString(String name, String value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setNString(index, value);
		}
	}

	public void setNull(String name, int sqlType) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setNull(index, sqlType);
		}
	}

	public void setObject(String name, Object value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setObject(index, value);
		}
	}

	public void setObject(String name, Object value, int targetSqlType) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setObject(index, value, targetSqlType);
		}
	}

	public void setObject(String name, Object value, int targetSqlType, int scaleOrLength) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setObject(index, value, targetSqlType, scaleOrLength);
		}
	}

	public void setRef(String name, Ref value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setRef(index, value);
		}
	}

	public void setRowId(String name, RowId value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setRowId(index, value);
		}
	}

	public void setShort(String name, short value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setShort(index, value);
		}
	}

	public void setSQLXML(String name, SQLXML value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setSQLXML(index, value);
		}
	}

	public void setString(String name, String value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setString(index, value);
		}
	}

	public void setTime(String name, Time value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setTime(index, value);
		}
	}

	public void setTime(String name, Time value, Calendar cal) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setTime(index, value, cal);
		}
	}

	public void setTimestamp(String name, Timestamp value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setTimestamp(index, value);
		}
	}

	public void setTimestamp(String name, Timestamp value, Calendar cal) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setTimestamp(index, value, cal);
		}
	}

	@SuppressWarnings("deprecation")
	public void setUnicodeStream(String name, InputStream value, int length) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setUnicodeStream(index, value, length);
		}
	}

	public void setURL(String name, URL value) throws SQLException {
		for (Integer index : getIndexes(name)) {
			statement.setURL(index, value);
		}
	}

	public PreparedStatement getStatement() {
		return statement;
	}

	public boolean execute() throws SQLException {
		return statement.execute();
	}

	public ResultSet executeQuery() throws SQLException {
		return statement.executeQuery();
	}

	public int executeUpdate() throws SQLException {
		return statement.executeUpdate();
	}

	public void close() throws SQLException {
		statement.close();
	}

	public void addBatch() throws SQLException {
		statement.addBatch();
	}

	public int[] executeBatch() throws SQLException {
		return statement.executeBatch();
	}
	
	public ResultSet executeQuery(String sql) throws SQLException {
		return statement.executeQuery(sql);
	}

	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return statement.isWrapperFor(iface);
	}

	
	public int executeUpdate(String sql) throws SQLException {
		return statement.executeUpdate(sql);
	}


	public void setNull(int parameterIndex, int sqlType) throws SQLException {
		statement.setNull(parameterIndex, sqlType);
	}

	
	public int getMaxFieldSize() throws SQLException {
		return statement.getMaxFieldSize();
	}

	
	public void setBoolean(int parameterIndex, boolean x) throws SQLException {
		statement.setBoolean(parameterIndex, x);
	}

	
	public void setByte(int parameterIndex, byte x) throws SQLException {
		statement.setByte(parameterIndex, x);
	}

	
	public void setMaxFieldSize(int max) throws SQLException {
		statement.setMaxFieldSize(max);
	}

	
	public void setShort(int parameterIndex, short x) throws SQLException {
		statement.setShort(parameterIndex, x);
	}

	
	public int getMaxRows() throws SQLException {
		return statement.getMaxRows();
	}

	
	public void setInt(int parameterIndex, int x) throws SQLException {
		statement.setInt(parameterIndex, x);
	}

	
	public void setMaxRows(int max) throws SQLException {
		statement.setMaxRows(max);
	}

	
	public void setLong(int parameterIndex, long x) throws SQLException {
		statement.setLong(parameterIndex, x);
	}

	
	public void setEscapeProcessing(boolean enable) throws SQLException {
		statement.setEscapeProcessing(enable);
	}

	
	public void setFloat(int parameterIndex, float x) throws SQLException {
		statement.setFloat(parameterIndex, x);
	}

	
	public void setDouble(int parameterIndex, double x) throws SQLException {
		statement.setDouble(parameterIndex, x);
	}

	
	public int getQueryTimeout() throws SQLException {
		return statement.getQueryTimeout();
	}

	
	public void setQueryTimeout(int seconds) throws SQLException {
		statement.setQueryTimeout(seconds);
	}

	
	public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException {
		statement.setBigDecimal(parameterIndex, x);
	}

	
	public void setString(int parameterIndex, String x) throws SQLException {
		statement.setString(parameterIndex, x);
	}

	
	public void setBytes(int parameterIndex, byte[] x) throws SQLException {
		statement.setBytes(parameterIndex, x);
	}

	
	public void cancel() throws SQLException {
		statement.cancel();
	}

	
	public SQLWarning getWarnings() throws SQLException {
		return statement.getWarnings();
	}

	
	public void setDate(int parameterIndex, Date x) throws SQLException {
		statement.setDate(parameterIndex, x);
	}

	
	public void setTime(int parameterIndex, Time x) throws SQLException {
		statement.setTime(parameterIndex, x);
	}

	
	public void clearWarnings() throws SQLException {
		statement.clearWarnings();
	}

	
	public void setCursorName(String name) throws SQLException {
		statement.setCursorName(name);
	}

	
	public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException {
		statement.setTimestamp(parameterIndex, x);
	}

	
	public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException {
		statement.setAsciiStream(parameterIndex, x, length);
	}

	
	public boolean execute(String sql) throws SQLException {
		return statement.execute(sql);
	}

	
	@SuppressWarnings("deprecation")
	public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException {
		statement.setUnicodeStream(parameterIndex, x, length);
	}

	
	public ResultSet getResultSet() throws SQLException {
		return statement.getResultSet();
	}

	
	public void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException {
		statement.setBinaryStream(parameterIndex, x, length);
	}

	
	public int getUpdateCount() throws SQLException {
		return statement.getUpdateCount();
	}

	
	public boolean getMoreResults() throws SQLException {
		return statement.getMoreResults();
	}

	
	public void clearParameters() throws SQLException {
		statement.clearParameters();
	}

	
	public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException {
		statement.setObject(parameterIndex, x, targetSqlType);
	}

	
	public void setFetchDirection(int direction) throws SQLException {
		statement.setFetchDirection(direction);
	}

	
	public int getFetchDirection() throws SQLException {
		return statement.getFetchDirection();
	}

	
	public void setObject(int parameterIndex, Object x) throws SQLException {
		statement.setObject(parameterIndex, x);
	}

	
	public void setFetchSize(int rows) throws SQLException {
		statement.setFetchSize(rows);
	}

	
	public int getFetchSize() throws SQLException {
		return statement.getFetchSize();
	}

	
	public int getResultSetConcurrency() throws SQLException {
		return statement.getResultSetConcurrency();
	}

	public int getResultSetType() throws SQLException {
		return statement.getResultSetType();
	}

	
	public void addBatch(String sql) throws SQLException {
		statement.addBatch(sql);
	}

	
	public void clearBatch() throws SQLException {
		statement.clearBatch();
	}

	
	public void setCharacterStream(int parameterIndex, Reader reader, int length) throws SQLException {
		statement.setCharacterStream(parameterIndex, reader, length);
	}

	
	public void setRef(int parameterIndex, Ref x) throws SQLException {
		statement.setRef(parameterIndex, x);
	}

	
	public void setBlob(int parameterIndex, Blob x) throws SQLException {
		statement.setBlob(parameterIndex, x);
	}

	
	public void setClob(int parameterIndex, Clob x) throws SQLException {
		statement.setClob(parameterIndex, x);
	}

	
	public Connection getConnection() throws SQLException {
		return statement.getConnection();
	}

	
	public void setArray(int parameterIndex, Array x) throws SQLException {
		statement.setArray(parameterIndex, x);
	}

	
	public ResultSetMetaData getMetaData() throws SQLException {
		return statement.getMetaData();
	}

	
	public boolean getMoreResults(int current) throws SQLException {
		return statement.getMoreResults(current);
	}

	
	public void setDate(int parameterIndex, Date x, Calendar cal) throws SQLException {
		statement.setDate(parameterIndex, x, cal);
	}

	
	public ResultSet getGeneratedKeys() throws SQLException {
		return statement.getGeneratedKeys();
	}

	
	public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {
		statement.setTime(parameterIndex, x, cal);
	}

	
	public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {
		return statement.executeUpdate(sql, autoGeneratedKeys);
	}

	
	public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException {
		statement.setTimestamp(parameterIndex, x, cal);
	}

	
	public void setNull(int parameterIndex, int sqlType, String typeName) throws SQLException {
		statement.setNull(parameterIndex, sqlType, typeName);
	}

	
	public int executeUpdate(String sql, int[] columnIndexes) throws SQLException {
		return statement.executeUpdate(sql, columnIndexes);
	}

	
	public void setURL(int parameterIndex, URL x) throws SQLException {
		statement.setURL(parameterIndex, x);
	}

	
	public int executeUpdate(String sql, String[] columnNames) throws SQLException {
		return statement.executeUpdate(sql, columnNames);
	}

	
	public ParameterMetaData getParameterMetaData() throws SQLException {
		return statement.getParameterMetaData();
	}

	
	public void setRowId(int parameterIndex, RowId x) throws SQLException {
		statement.setRowId(parameterIndex, x);
	}

	
	public void setNString(int parameterIndex, String value) throws SQLException {
		statement.setNString(parameterIndex, value);
	}

	
	public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {
		return statement.execute(sql, autoGeneratedKeys);
	}

	
	public void setNCharacterStream(int parameterIndex, Reader value, long length) throws SQLException {
		statement.setNCharacterStream(parameterIndex, value, length);
	}

	
	public void setNClob(int parameterIndex, NClob value) throws SQLException {
		statement.setNClob(parameterIndex, value);
	}

	
	public void setClob(int parameterIndex, Reader reader, long length) throws SQLException {
		statement.setClob(parameterIndex, reader, length);
	}

	
	public boolean execute(String sql, int[] columnIndexes) throws SQLException {
		return statement.execute(sql, columnIndexes);
	}

	
	public void setBlob(int parameterIndex, InputStream inputStream, long length) throws SQLException {
		statement.setBlob(parameterIndex, inputStream, length);
	}

	
	public void setNClob(int parameterIndex, Reader reader, long length) throws SQLException {
		statement.setNClob(parameterIndex, reader, length);
	}

	
	public boolean execute(String sql, String[] columnNames) throws SQLException {
		return statement.execute(sql, columnNames);
	}

	
	public void setSQLXML(int parameterIndex, SQLXML xmlObject) throws SQLException {
		statement.setSQLXML(parameterIndex, xmlObject);
	}

	
	public void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength) throws SQLException {
		statement.setObject(parameterIndex, x, targetSqlType, scaleOrLength);
	}

	
	public int getResultSetHoldability() throws SQLException {
		return statement.getResultSetHoldability();
	}

	
	public boolean isClosed() throws SQLException {
		return statement.isClosed();
	}

	
	public void setPoolable(boolean poolable) throws SQLException {
		statement.setPoolable(poolable);
	}

	
	public boolean isPoolable() throws SQLException {
		return statement.isPoolable();
	}

	
	public void setAsciiStream(int parameterIndex, InputStream x, long length) throws SQLException {
		statement.setAsciiStream(parameterIndex, x, length);
	}


	
	public void setBinaryStream(int parameterIndex, InputStream x, long length) throws SQLException {
		statement.setBinaryStream(parameterIndex, x, length);
	}

	
	public void setCharacterStream(int parameterIndex, Reader reader, long length) throws SQLException {
		statement.setCharacterStream(parameterIndex, reader, length);
	}

	
	public void setAsciiStream(int parameterIndex, InputStream x) throws SQLException {
		statement.setAsciiStream(parameterIndex, x);
	}

	
	public void setBinaryStream(int parameterIndex, InputStream x) throws SQLException {
		statement.setBinaryStream(parameterIndex, x);
	}

	
	public void setCharacterStream(int parameterIndex, Reader reader) throws SQLException {
		statement.setCharacterStream(parameterIndex, reader);
	}

	
	public void setNCharacterStream(int parameterIndex, Reader value) throws SQLException {
		statement.setNCharacterStream(parameterIndex, value);
	}

	
	public void setClob(int parameterIndex, Reader reader) throws SQLException {
		statement.setClob(parameterIndex, reader);
	}

	
	public void setBlob(int parameterIndex, InputStream inputStream) throws SQLException {
		statement.setBlob(parameterIndex, inputStream);
	}

	
	public void setNClob(int parameterIndex, Reader reader) throws SQLException {
		statement.setNClob(parameterIndex, reader);
	}
}
